package team.wuyou.util.jdbc;

import java.sql.*;
import java.util.*;



public class ExecuteQuery {
    /**
     * @return void
     * @Description //TODO
     * @Param person\wang\jdbc\ExecuteQuery.java
     * @Date 2023/11/9 10:22
     * @Wang 这是个查询工具类
     * 可能需要修改*字符
     **/
    public static void main(String[] args) throws Exception {
//        Map map = new HashMap();
//        //map.put("id","1");
//        map.put("state","3");
//        Query("planInfo",map);
       // executeQuery("select remark from planInfo where 1=1 and state = 0");




        //查询数据库
        String a="登陆成功";
        HashMap map = new HashMap();
        map.put("username","abc");
        map.put("password","123");
        String user;
        try {
            user = Query("user", map);
             user = user.substring(1, user.length()-2);
            String[] split = user.split(",");
            String password=split[0].split("=")[1];
            String username=split[2].split("=")[1];
            System.out.println(username);
            System.out.println(password);
            System.out.println(user);
//            String[] replace = user.split("=");
//            String[] split = replace[1].split("}");
//            int userValue=Integer.parseInt( split[0]);
//            System.out.println(userValue);

//            if (userValue>0){
//                System.out.println(a);
//
//            }else {
//                a="密码错误";
//                System.out.println(a);
//            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }




    }
        static {
        try {
            Class.forName(Propertity.Driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
    public static List QueryList(String TableName, Map<String, Object> map) throws Exception {
        List list = new ArrayList();
        List list2 = new ArrayList();
        //列
        //获取链接
        Connection conn = DriverManager.getConnection( Propertity.url, Propertity.username, Propertity.password);
        String sql="select * from "+TableName+" where 1+1 ";
        Set<Map.Entry<String,Object>> set =  map.entrySet();
        int j=1;

        for (Map.Entry entry: set) {
            if (entry.getKey()!=""&&entry.getKey()!=null&&entry.getValue()!=null&&entry.getValue()!="") {
                sql += "AND " + entry.getKey() + " = ? ";
                list.add(entry.getValue());
                ++j;
            }
        }
        System.out.println(sql);
        //载体
        PreparedStatement ps = conn.prepareStatement(sql);
        //将值付给 ？
        setData(ps,list);
        //执行sql
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //统计列的数量
        int columnCount = metaData.getColumnCount();
        //列数
        //System.out.println("columnCount："+columnCount);
        //获取每一列的下一个数据是否有值
        while (rs.next()){
            Map map2 =new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                //获取列名
                //user user=new user();
                String columnName = metaData.getColumnName(i);
                //输出列明
                //System.out.print("columnName:"+columnName);
                //根据列名获取
                Object object = rs.getObject(columnName);
                //列名为key键，信息为value输入map
                map2.put(columnName,object);
            }
            //每获取一行数据添加一次
            list2.add(map2);

        }
//        for (int i = 0; i < list2.size(); i++) {
//            //输出值
//            System.out.println(list2.get(i));
//        }
//        String a="";
//        for (int i = 0; i <list2.size() ; i++) {
//            a+=list2.get(i)+",";
//        }
        //System.out.println(a);
        return list2;
    }/**
    * @Description //TODO
    * @Param person\wang\jdbc\ExecuteQuery.java
    * @return java.util.List
    * @Date 2023/12/14 20:22
    * @Wang
    *   模糊查询
    **/
    public static List QueryLikeLimit(String TableName,String page, Map<String, Object> map) throws Exception {
        List list = new ArrayList();
        List list2 = new ArrayList();
        //列
        //获取链接
        Connection conn = DriverManager.getConnection( Propertity.url, Propertity.username, Propertity.password);
        String sql="select * from "+TableName+" where 1+1 ";
        Set<Map.Entry<String,Object>> set =  map.entrySet();
        int j=1;

        for (Map.Entry entry: set) {
            if (entry.getKey()!=""&&entry.getKey()!=null&&entry.getValue()!=null&&entry.getValue()!="") {
                sql += "AND " + entry.getKey() + " like ? ";
                list.add(entry.getValue());
                ++j;
            }
        }
        sql=sql+" limit "+page+" ,10";
        System.out.println(sql);
        //载体
        PreparedStatement ps = conn.prepareStatement(sql);
        //将值付给 ？
        setData(ps,list);
        //执行sql
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //统计列的数量
        int columnCount = metaData.getColumnCount();
        //列数
        //System.out.println("columnCount："+columnCount);
        //获取每一列的下一个数据是否有值
        while (rs.next()){
            Map map2 =new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                //获取列名
                //user user=new user();
                String columnName = metaData.getColumnName(i);
                //输出列明
                //System.out.print("columnName:"+columnName);
                //根据列名获取
                Object object = rs.getObject(columnName);
                //列名为key键，信息为value输入map
                map2.put(columnName,object);
            }
            //每获取一行数据添加一次
            list2.add(map2);

        }
//        for (int i = 0; i < list2.size(); i++) {
//            //输出值
//            System.out.println(list2.get(i));
//        }
//        String a="";
//        for (int i = 0; i <list2.size() ; i++) {
//            a+=list2.get(i)+",";
//        }
        //System.out.println(a);
        return list2;
    }
    public static List Querylimit(String TableName,String page, Map<String, Object> map) throws Exception {
        List list = new ArrayList();
        List list2 = new ArrayList();
        //列
        //获取链接
        Connection conn = DriverManager.getConnection( Propertity.url, Propertity.username, Propertity.password);
        String sql="select * from "+TableName+" where 1+1 ";
        Set<Map.Entry<String,Object>> set =  map.entrySet();
        int j=1;

        for (Map.Entry entry: set) {
            if (entry.getKey()!=""&&entry.getKey()!=null&&entry.getValue()!=null&&entry.getValue()!="") {
                sql += "AND " + entry.getKey() + " = ? ";
                list.add(entry.getValue());
                ++j;
            }
        }
        sql=sql+" limit "+page+" ,10";
        System.out.println(sql);
        //载体
        PreparedStatement ps = conn.prepareStatement(sql);
        //将值付给 ？
        setData(ps,list);
        //执行sql
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //统计列的数量
        int columnCount = metaData.getColumnCount();
        //列数
        //System.out.println("columnCount："+columnCount);
        //获取每一列的下一个数据是否有值
        while (rs.next()){
            Map map2 =new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                //获取列名
                //user user=new user();
                String columnName = metaData.getColumnName(i);
                //输出列明
                //System.out.print("columnName:"+columnName);
                //根据列名获取
                Object object = rs.getObject(columnName);
                //列名为key键，信息为value输入map
                map2.put(columnName,object);
            }
            //每获取一行数据添加一次
            list2.add(map2);

        }
//        for (int i = 0; i < list2.size(); i++) {
//            //输出值
//            System.out.println(list2.get(i));
//        }
//        String a="";
//        for (int i = 0; i <list2.size() ; i++) {
//            a+=list2.get(i)+",";
//        }
        //System.out.println(a);
        return list2;
    }
    public static String Query(String TableName, Map<String, Object> map) throws Exception {
        List list = new ArrayList();
        List list2 = new ArrayList();
        //列
        //获取链接
        Connection conn = DriverManager.getConnection( Propertity.url, Propertity.username, Propertity.password);
        String sql="select * from "+TableName+" where 1+1 ";
        Set<Map.Entry<String,Object>> set =  map.entrySet();
        int j=1;

        for (Map.Entry entry: set) {
            if (entry.getKey()!=""&&entry.getKey()!=null&&entry.getValue()!=null&&entry.getValue()!="") {
                sql += "AND " + entry.getKey() + " = ? ";
                list.add(entry.getValue());
                ++j;
            }
        }
        System.out.println(sql);
        //载体
        PreparedStatement ps = conn.prepareStatement(sql);
        //将值付给 ？
        setData(ps,list);
        //执行sql
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //统计列的数量
        int columnCount = metaData.getColumnCount();
        //列数
        //System.out.println("columnCount："+columnCount);
        //获取每一列的下一个数据是否有值
        while (rs.next()){
            Map map2 =new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                //获取列名
                //user user=new user();
                String columnName = metaData.getColumnName(i);
                //输出列明
                //System.out.print("columnName:"+columnName);
                //根据列名获取
                Object object = rs.getObject(columnName);
                //列名为key键，信息为value输入map
                map2.put(columnName,object);
            }
            //每获取一行数据添加一次
            list2.add(map2);
        }
//        for (int i = 0; i < list2.size(); i++) {
//            //输出值
//            System.out.println(list2.get(i));
//        }
        String a="";
        for (int i = 0; i <list2.size() ; i++) {
            a+=list2.get(i)+",";
        }
        //System.out.println(a);
        return a;
    }
    public static void setData(PreparedStatement ps,List list) throws SQLException {
        for (int i = 0; i < list.size(); i++) {
            ps.setObject(i+1,list.get(i));
        }
    }
    public static List<?> executeQuery(String sql)throws Exception{
        List list2 = new ArrayList();
        Connection conn = DriverManager.getConnection( Propertity.url, Propertity.username, Propertity.password);
        //System.out.println(sql);
        //载体
        PreparedStatement ps = conn.prepareStatement(sql);
        //执行sql
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData metaData = rs.getMetaData();
        //统计列的数量
        int columnCount = metaData.getColumnCount();
        //列数
        //System.out.println("columnCount："+columnCount);
        //获取每一列的下一个数据是否有值
        while (rs.next()){
            Map map2 =new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                //获取列名
                //user user=new user();
                String columnName = metaData.getColumnName(i);
                //输出列明
                //System.out.print("columnName:"+columnName);
                //根据列名获取
                Object object = rs.getObject(columnName);
                //列名为key键，信息为value输入map
                map2.put(columnName,object);
            }
            //每获取一行数据添加一次
            list2.add(map2);
        }
//        for (int i = 0; i < list2.size(); i++) {
//            //输出值
//           // System.out.println(list2.get(i));
//        }
//        String a="";
//        for (int i = 0; i <list2.size() ; i++) {
//            a+=list2.get(i)+",";
//        }
//        //System.out.println(a);
//
//         a = a.replace("=", ":");

        return list2;

    }

    public static String toJson(List<Map<String, Object>> list) {
        StringBuilder json = new StringBuilder();
        json.append("[");
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> map = list.get(i);
            json.append("{");
            boolean isFirst = true;
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                if (!isFirst) {
                    json.append(",");
                }
                json.append("\"").append(entry.getKey()).append("\":");

                    json.append("\"").append(entry.getValue()).append("\"");

                isFirst = false;
            }
            json.append("}");
            if (i != list.size() - 1) {
                json.append(",");
            }
        }
        json.append("]");
        return json.toString();
    }



}

